package vn.edu.saigontech.ncms.web.system;

import java.math.BigInteger;
import java.util.Collection;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

import vn.edu.saigontech.ncms.model.info.AbstractReceipt;
import vn.edu.saigontech.ncms.model.info.OfferingMember;
import vn.edu.saigontech.ncms.model.info.Student;
import vn.edu.saigontech.ncms.model.info.Waiting;
import vn.edu.saigontech.ncms.service.OfferingMemberService;
import vn.edu.saigontech.ncms.service.impl.ProgramServiceImpl;
import vn.edu.saigontech.ncms.service.impl.StudentServiceImpl;
import vn.edu.saigontech.ncms.service.impl.WaitingServiceImpl;
import vn.edu.saigontech.ncms.util.DateUtil;
import vn.edu.saigontech.ncms.util.ExcelUtil;
import vn.edu.saigontech.ncms.web.BaseAction;

@SuppressWarnings("serial")
public class ConsultativeReportAction extends BaseAction {
	
	private int month;
	private int year;
	
	private final Long preChildrenProgramId = 9L;
	private final Long childrenProgramId = 17L;
	private final Long teenagerProgramId = 12L;
	private final Long generalProgramId = 13L;
	private final Long toeicProgramId = 14L;
	private final Long toeflProgramId = 7L;
	
	private final Long sgtId = 0L;
	private final Long qtscId = 1L;
	private final Long mnttId = 2L;
	private final Long sosId = 5L;
	
	private final String preChildrenProgramName = "Pre-Children";
	private final String childrenProgramName = "Children";
	private final String teenagerProgramName = "Teenager";
	private final String generalProgramName = "General";
	private final String toeicProgramName = "Toeic";
	private final String toeflProgramName = "Toefl";
	
	private final String sgtName = "SGT";
	private final String qtscName = "QTSC";
	private final String mnttName = "MNTT";
	private final String sosName = "SOS";
	
	Long[] programIds = new Long[] { preChildrenProgramId, childrenProgramId, teenagerProgramId, generalProgramId, toeicProgramId, toeflProgramId };
	String[] programNames = new String[] { preChildrenProgramName, childrenProgramName, teenagerProgramName, generalProgramName, toeicProgramName, toeflProgramName };
	Long[] typeIds = new Long[] { sgtId, qtscId, mnttId, sosId};
	String[] typeNames = new String[] { sgtName, qtscName, mnttName, sosName};
	
	HSSFFont fontBold18;
	HSSFFont fontBold12;
	HSSFFont fontNormal12;
	HSSFFont fontItalicl2;
	HSSFFont fontBold10;
	HSSFFont fontNormal8;
	
	HSSFCellStyle styleCenter_B18;
	HSSFCellStyle style_B12;
	HSSFCellStyle styleCenter_B12;
	HSSFCellStyle styleCenter_N12;
	HSSFCellStyle style_N12;
	HSSFCellStyle styleCenter_N12_percent;
	HSSFCellStyle styleCenter_B12_percent;
	HSSFCellStyle style_I12;
	HSSFCellStyle style_B10;
	HSSFCellStyle style_B10b;
	HSSFCellStyle style_N8;

	public String exportConsultativeExcel() {
		//List<USD> usds = (List<USD>) USDService.INSTANCE.findByMonth(this.month, this.year);
		RESPONSE().setContentType("application/vnd.ms-excel");
		RESPONSE().setHeader("Content-disposition","attachment; filename=\""+ "Consultative Report " + this.month + "-" + this.year + ".xls\"");
		
		try {
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet sheet = workbook.createSheet("Currency " + this.month + "-" + this.year);
			sheet.setDefaultRowHeightInPoints(16);
			
			fontBold18 = ExcelUtil.makeFont(workbook, "Times New Roman", 18, true);
			fontBold12 = ExcelUtil.makeFont(workbook, "Times New Roman", 12, true);
			fontNormal12 = ExcelUtil.makeFont(workbook, "Times New Roman", 12, false);
			fontItalicl2 = ExcelUtil.makeFont(workbook, "Times New Roman", 12, false);
			fontItalicl2.setItalic(true);
			fontBold10 = ExcelUtil.makeFont(workbook, "Times New Roman", 10, true);
			fontNormal8 = ExcelUtil.makeFont(workbook, "Times New Roman", 8, false);
			
			styleCenter_B18 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold18);
			style_B12 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_CENTER, fontBold12);
			styleCenter_B12 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold12);
			ExcelUtil.makeBorder(styleCenter_B12);
			styleCenter_N12 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontNormal12);
			ExcelUtil.makeBorder(styleCenter_N12);
			style_N12 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_CENTER, fontNormal12);
			ExcelUtil.makeBorder(style_N12);
			styleCenter_N12_percent = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontNormal12);
			styleCenter_N12_percent.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
			ExcelUtil.makeBorder(styleCenter_N12_percent);
			styleCenter_B12_percent = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold12);
			styleCenter_B12_percent.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
			ExcelUtil.makeBorder(styleCenter_B12_percent);
			style_I12 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_CENTER, fontItalicl2);
			style_B10 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_CENTER, fontBold10);
			style_B10b = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold10);
			ExcelUtil.makeBorder(style_B10b);
			style_N8 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_CENTER, fontNormal8);
			
			/* HEADER - start */
			HSSFRow row = sheet.createRow(0);
			row.setHeightInPoints(42);
			
			row = sheet.createRow(1);
			row.setHeightInPoints(13);
			HSSFCell cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(style_B10);
			String name = this.i18n("currency.report");
			cell.setCellValue(new HSSFRichTextString(name));
			sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 3));
			
			row = sheet.createRow(2); // create empty row
			
			row = sheet.createRow(3);			
			row.setHeightInPoints(24);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B18);
			name = "BÁO CÁO TÌNH HÌNH TƯ VẤN";
			cell.setCellValue(new HSSFRichTextString(name));
			sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 7)); // first row, last row, first column, last column
			
			row = sheet.createRow(4);			
			row.setHeightInPoints(24);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B18);
			name = "CHƯƠNG TRÌNH ANH NGỮ BUỔI TỐI";
			cell.setCellValue(new HSSFRichTextString(name));
			sheet.addMergedRegion(new CellRangeAddress(4, 4, 0, 7)); // first row, last row, first column, last column
			
			row = sheet.createRow(5);
			row.setHeightInPoints(24);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B18);
			name = "THÁNG "  + this.month + "/" + this.year;
			cell.setCellValue(new HSSFRichTextString(name));
			sheet.addMergedRegion(new CellRangeAddress(5, 5, 0, 7)); // first row, last row, first column, last column
			/* HEADER - end */
			
			/* Registration statistic */
			workbook = this.createRegistrationStat(workbook);
			workbook = this.createWaitingStat(workbook);
			workbook = this.createContinueStudyStat(workbook);
			workbook = this.createFreeTuitionStat(workbook);
			workbook.write(RESPONSE().getOutputStream());
		} catch (Exception e) {
			
		}
		this.addLoggingDetail(BaseAction.REPORT, "logging.consultative.report", this.month, this.year+"");
		return NONE;
	}
	
	private HSSFWorkbook createFreeTuitionStat(HSSFWorkbook workbook) {
		HSSFSheet sheet = workbook.getSheetAt(0);
		int rowCount = sheet.getLastRowNum();
		
		HSSFRow row = sheet.createRow(++rowCount); // create empty row
		
		row = sheet.createRow(++rowCount);
		row.setHeightInPoints(17);
		HSSFCell cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
		cell.setCellStyle(style_B12);
		String name = "VII. TÌNH HÌNH HỌC VIÊN MIỄN PHÍ:";
		cell.setCellValue(new HSSFRichTextString(name));
		sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount, 0, 7));
		
		row = sheet.createRow(++rowCount);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 0, this.i18n("no"), 256*5, styleCenter_B12);
		sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount+1, 0, 0)); // first row, last row, first column, last column
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 1, "Loại", 256*18, styleCenter_B12);
		sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount+1, 1, 1));
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 2, "Số HV hỏi thông tin", 256*13, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 3, "Số HV thi xếp lớp", 256*13, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 4, "Số HV ghi danh", 256*13, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 5, "Số HV chờ lớp", 256*13, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 6, "Số HV đang theo học", 256*18, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 7, "Số HV tạm ngưng", 256*14, styleCenter_B12);
		
		row = sheet.createRow(++rowCount); // next row
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 0, "", 256*5, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 1, "", 256*18, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 2, "(1)", 256*13, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 3, "(2)", 256*13, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 4, "(3)", 256*13, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 5, "(4)", 256*13, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 6, "(5)", 256*18, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 7, "(6)", 256*14, styleCenter_B12);
		
		int firstRow = rowCount + 2;
		for (int i = 0; i < typeIds.length; i++) {
			Object[] values = (Object[]) StudentServiceImpl.INSTANCE.findFreeTuitionByDateNTuition(this.month, this.year, typeIds[i]);
			int nStudents = ((BigInteger)values[0]).intValue();
			int nPlacementTests = ((BigInteger)values[1]).intValue();
			int nRegisters = ((BigInteger)values[2]).intValue();
			int nWaitings = ((BigInteger)values[3]).intValue();
			int nStudyings = ((BigInteger)values[4]).intValue();
			int nPostpones = 0;
			
			row = sheet.createRow(++rowCount);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_N12);
			cell.setCellValue(i+1);
			
			cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(style_N12);
			cell.setCellValue(new HSSFRichTextString(typeNames[i]));
			
			cell = row.createCell(2, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_N12);
			cell.setCellValue(nStudents);
			
			cell = row.createCell(3, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_N12);
			cell.setCellValue(nPlacementTests);
			
			cell = row.createCell(4, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_N12);
			cell.setCellValue(nRegisters);
			
			cell = row.createCell(5, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_N12);
			cell.setCellValue(nWaitings);
			
			cell = row.createCell(6, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_N12);
			cell.setCellValue(nStudyings);
			
			cell = row.createCell(7, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_N12);
			cell.setCellValue(nPostpones);
		}
		int lastRow = rowCount + 1;
		row = sheet.createRow(++rowCount);
		cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
		cell.setCellStyle(styleCenter_B12);
		cell.setCellValue(new HSSFRichTextString("Tổng cộng"));
		sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount, 0, 1)); // first row, last row, first column, last column
		
		cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
		cell.setCellStyle(styleCenter_B12);
		
		cell = row.createCell(2, HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellStyle(styleCenter_B12);
		cell.setCellFormula("SUM(C"+firstRow+":C"+lastRow+")");
		
		cell = row.createCell(3, HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellStyle(styleCenter_B12);
		cell.setCellFormula("SUM(D"+firstRow+":D"+lastRow+")");
		
		cell = row.createCell(4, HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellStyle(styleCenter_B12);
		cell.setCellFormula("SUM(E"+firstRow+":E"+lastRow+")");
		
		cell = row.createCell(5, HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellStyle(styleCenter_B12);
		cell.setCellFormula("SUM(F"+firstRow+":F"+lastRow+")");
		
		cell = row.createCell(6, HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellStyle(styleCenter_B12);
		cell.setCellFormula("SUM(G"+firstRow+":G"+lastRow+")");
		
		cell = row.createCell(7, HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellStyle(styleCenter_B12);
		cell.setCellFormula("SUM(H"+firstRow+":H"+lastRow+")");
		
		return workbook;
	}

	private HSSFWorkbook createRegistrationStat(HSSFWorkbook workbook) {
		Date date2 = DateUtil.backToPreviousMonth(DateUtil.getStartDateOfMonth(this.month, this.year));
		int month2 = DateUtil.getMonth(date2)+1;
		int year2 = DateUtil.getYear(date2);
		
		HSSFSheet sheet = workbook.getSheetAt(0);
		int rowCount = sheet.getLastRowNum();		
		
		HSSFRow row = sheet.createRow(rowCount++); // create empty row
		
		row = sheet.createRow(++rowCount);
		row.setHeightInPoints(17);
		HSSFCell cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
		cell.setCellStyle(style_B12);
		String name = "I. TÌNH HÌNH GHI DANH:";
		cell.setCellValue(new HSSFRichTextString(name));
		sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount, 0, 7));
		
		row = sheet.createRow(++rowCount);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 0, this.i18n("no"), 256*5, styleCenter_B12);
		sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount+1, 0, 0)); // first row, last row, first column, last column
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 1, this.i18n("program"), 256*18, styleCenter_B12);
		sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount+1, 1, 1));
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 2, "Số HV hỏi thông tin", 256*13, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 3, "Số HV thi xếp lớp", 256*13, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 4, "Số HV ghi danh", 256*13, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 5, "Số HV chờ lớp", 256*13, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 6, "Tỉ lệ phần trăm", 256*18, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 7, "Tỉ lệ tháng " + month2 + "/" + year2, 256*14, styleCenter_B12);
		sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount+1, 7, 7));
		
		row = sheet.createRow(++rowCount); // next row
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 0, "", 256*5, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 1, "", 256*18, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 2, "(1)", 256*13, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 3, "(2)", 256*13, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 4, "(3)", 256*13, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 5, "(4)", 256*13, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 6, "(5)=(3)+(4)/(1)", 256*18, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 7, "", 256*14, styleCenter_B12);
		
		/* Pre - children */
		int sumConsult = 0;
		int sumPlacementTest = 0;
		int sumRegister = 0;
		int sumWaiting = 0;
		
		for (int i = 0; i < programIds.length; i++) {
			Object[] values = (Object[]) StudentServiceImpl.INSTANCE.findByDateNProgram(this.month, this.year, ProgramServiceImpl.INSTANCE.findById(programIds[i]));
//			String pname = values[0]+"";
			int nConsults = ((BigInteger)values[1]).intValue();
			int nPlacementTests = ((BigInteger)values[2]).intValue();
			int nRegisters = ((BigInteger)values[3]).intValue();
			int nWaitings = ((BigInteger)values[4]).intValue();
			
			sumConsult += nConsults;
			sumPlacementTest += nPlacementTests;
			sumRegister += nRegisters;
			sumWaiting += nWaitings;
			
			double nPercents = (nConsults != 0? ((double)nRegisters + (double)nWaitings) / (double)nConsults : 0);
			
			Object[] preiousValues = (Object[]) StudentServiceImpl.INSTANCE.findByDateNProgram(month2, year2, ProgramServiceImpl.INSTANCE.findById(programIds[i]));
			double nPrevPercents = (((BigInteger)preiousValues[1]).intValue() != 0? ((double)((BigInteger)preiousValues[3]).intValue() + (double)((BigInteger)preiousValues[4]).intValue()) / (double)((BigInteger)preiousValues[1]).intValue() : 0);
			
			row = sheet.createRow(++rowCount);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_N12);
			cell.setCellValue(i+1);
			
			cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(style_N12);
			cell.setCellValue(new HSSFRichTextString(programNames[i]));
			
			cell = row.createCell(2, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_N12);
			cell.setCellValue(nConsults);
			
			cell = row.createCell(3, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_N12);
			cell.setCellValue(nPlacementTests);
			
			cell = row.createCell(4, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_N12);
			cell.setCellValue(nRegisters);
			
			cell = row.createCell(5, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_N12);
			cell.setCellValue(nWaitings);
			
			cell = row.createCell(6, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_N12_percent);
			cell.setCellValue(nPercents);
			
			cell = row.createCell(7, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_N12_percent);
			cell.setCellValue(nPrevPercents);
		}
		
		row = sheet.createRow(++rowCount);
		cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
		cell.setCellStyle(styleCenter_B12);
		cell.setCellValue(new HSSFRichTextString("Tổng cộng"));
		sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount, 0, 1)); // first row, last row, first column, last column
		
		cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
		cell.setCellStyle(styleCenter_B12);
		
		cell = row.createCell(2, HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellStyle(styleCenter_B12);
		cell.setCellValue(sumConsult);
		sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount+1, 2, 2)); // first row, last row, first column, last column
		
		cell = row.createCell(3, HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellStyle(styleCenter_B12);
		cell.setCellValue(sumPlacementTest);
		
		cell = row.createCell(4, HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellStyle(styleCenter_B12);
		cell.setCellValue(sumRegister);
		
		cell = row.createCell(5, HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellStyle(styleCenter_B12);
		cell.setCellValue(sumWaiting);
		
		cell = row.createCell(6, HSSFCell.CELL_TYPE_STRING);
		cell.setCellStyle(styleCenter_B12);
		sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount+1, 6, 6)); // first row, last row, first column, last column
		cell.setCellValue(new HSSFRichTextString(""));
		
		cell = row.createCell(7, HSSFCell.CELL_TYPE_STRING);
		cell.setCellStyle(styleCenter_B12);
		sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount+1, 7, 7)); // first row, last row, first column, last column
		cell.setCellValue(new HSSFRichTextString(""));
		
		/* percent */
		row = sheet.createRow(++rowCount);
		cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
		cell.setCellStyle(styleCenter_B12);
		cell.setCellValue(new HSSFRichTextString("Tỉ lệ (so với số người hỏi thông tin)"));
		row.setHeightInPoints(35);
		sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount, 0, 1)); // first row, last row, first column, last column
		
		cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
		cell.setCellStyle(styleCenter_B12);
		
		cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
		cell.setCellStyle(styleCenter_B12);
		
		cell = row.createCell(3, HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellStyle(styleCenter_B12_percent);
		cell.setCellValue((double)sumPlacementTest / (double)sumConsult);
		
		cell = row.createCell(4, HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellStyle(styleCenter_B12_percent);
		cell.setCellValue((double)sumRegister / (double)sumConsult);
		
		cell = row.createCell(5, HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellStyle(styleCenter_B12_percent);
		cell.setCellValue((double)sumWaiting / (double)sumConsult);
		
		cell = row.createCell(6, HSSFCell.CELL_TYPE_STRING);
		cell.setCellStyle(styleCenter_B12);
		
		cell = row.createCell(7, HSSFCell.CELL_TYPE_STRING);
		cell.setCellStyle(styleCenter_B12);
		
		return workbook;
	}
	
	private HSSFWorkbook createWaitingStat(HSSFWorkbook workbook) {
		HSSFSheet sheet = workbook.getSheetAt(0);
		int rowCount = sheet.getLastRowNum();
		
		HSSFRow row = sheet.createRow(++rowCount); // create empty row
		row = sheet.createRow(++rowCount); // create empty row
		
		row = sheet.createRow(rowCount);
		row.setHeightInPoints(17);
		HSSFCell cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
		cell.setCellStyle(style_B12);
		String name = "II. TÌNH HÌNH HỌC VIÊN CHỜ LỚP:";
		cell.setCellValue(new HSSFRichTextString(name));
		sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount, 0, 7));
		
		row = sheet.createRow(++rowCount);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 0, this.i18n("no"), 256*5, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 1, this.i18n("program"), 256*18, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 2, "Lý do đang chờ lớp", 256*13, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 3, "", 256*5, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 4, "", 256*5, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 5, "", 256*5, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 6, "", 256*5, styleCenter_B12);
		sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount, 2, 6));
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 7, "Số luợng HV", 256*13, styleCenter_B12);
		
		int sumWaiting = 0;
		
		for (int i = 0; i < programIds.length; i++) {
			List<Waiting> waitings = WaitingServiceImpl.INSTANCE.findByDateNProgram(this.month, this.year, programIds[i]);
			int nStudents = 0;
//			String pname = "";
			String pcomment = ""; 
			if (waitings.size() != 0) { 
//				pname = waitings.get(0).getLevel().getProgram().getName();
				pcomment = waitings.get(0).getComment();
				nStudents = waitings.size();
			}
			
			sumWaiting += nStudents;
			
			row = sheet.createRow(++rowCount);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_N12);
			cell.setCellValue(i+1);
			
			cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(style_N12);
			cell.setCellValue(new HSSFRichTextString(programNames[i]));
			
			cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(style_N12);
			cell.setCellValue(new HSSFRichTextString(pcomment));
			
			cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B12);
			cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B12);
			cell = row.createCell(5, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B12);
			cell = row.createCell(6, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B12);
						
			sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount, 2, 6));
			
			cell = row.createCell(7, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_N12);
			cell.setCellValue(nStudents);
		}
		
		row = sheet.createRow(++rowCount);
		cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
		cell.setCellStyle(styleCenter_B12);
		cell.setCellValue(new HSSFRichTextString("Tổng cộng"));
		sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount, 0, 6)); // first row, last row, first column, last column
		
		cell = row.createCell(7, HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellStyle(styleCenter_B12);
		cell.setCellValue(sumWaiting);
		
		return workbook;
	}

	private HSSFWorkbook createContinueStudyStat(HSSFWorkbook workbook) {
		Date date2 = DateUtil.backToPreviousMonth(DateUtil.getStartDateOfMonth(this.month, this.year));
		int month2 = DateUtil.getMonth(date2)+1;
		int year2 = DateUtil.getYear(date2);
		
		Date startDate = DateUtil.getStartDateOfMonth(month, year);
		Date endDate = DateUtil.getEndDateOfMonth(month, year);

		Date startDate2 = DateUtil.getStartDateOfMonth(month2, year2);
		Date endDate2 = DateUtil.getEndDateOfMonth(month2, year2);
		
		HSSFSheet sheet = workbook.getSheetAt(0);
		int rowCount = sheet.getLastRowNum();		
		
		HSSFRow row = sheet.createRow(rowCount++); // create empty row
		
		row = sheet.createRow(rowCount);
		row.setHeightInPoints(17);
		HSSFCell cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
		cell.setCellStyle(style_B12);
		String name = "III. TÌNH HÌNH TÁI GHI DANH:";
		cell.setCellValue(new HSSFRichTextString(name));
		sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount, 0, 7));
		
		row = sheet.createRow(++rowCount);
		row.setHeightInPoints(18);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 0, this.i18n("no"), 256*5, styleCenter_B12);
		sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount+2, 0, 0)); // first row, last row, first column, last column
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 1, this.i18n("program"), 256*18, styleCenter_B12);
		sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount+2, 1, 1)); 
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 2, "Số HV trong lớp kết thúc", 256*13, styleCenter_B12);
		sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount+1, 2, 2));
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 3, "Số HV ghi danh lại", 256*13, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 4, "", 256*13, styleCenter_B12);
		sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount, 3, 4));
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 5, "Tỉ lệ (%)", 256*13, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 6, "", 256*18, styleCenter_B12);
		sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount+1, 5, 6));
		sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount+1, 6, 6));
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 7, "Tỉ lệ tháng " + month2 + "/" + year2, 256*14, styleCenter_B12);
		sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount+2, 7, 7));
		
		row = sheet.createRow(++rowCount); // next row
		row.setHeightInPoints(18);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 0, "", 256*5, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 1, "", 256*18, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 2, "", 256*13, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 3, "HV đóng HP", 256*13, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 4, "HV miễn phí", 256*13, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 5, "", 256*13, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 6, "", 256*18, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 7, "", 256*14, styleCenter_B12);
		
		row = sheet.createRow(++rowCount); // next row
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 0, "", 256*5, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 1, "", 256*18, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 2, "(1)", 256*13, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 3, "(2)", 256*13, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 4, "(3)", 256*13, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 5, "(4)=(2)+(3)/(1)", 256*13, styleCenter_B12);
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 6, "", 256*18, styleCenter_B12);
		sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount, 5, 6));
		ExcelUtil.makeColumnLabel(workbook, sheet, row, 7, "", 256*14, styleCenter_B12);

		int firstRow = rowCount+2;
		for (int i = 0; i < programIds.length; i++) {
			double free = 0;
			double noFree = 0;
			
			Collection<OfferingMember> members = OfferingMemberService.INSTANCE
					.findByDateAndProgram(startDate, endDate, ProgramServiceImpl.INSTANCE.findById(programIds[i]));
			Student student = null;
			int size = members.size();
			
			for (OfferingMember member : members) {
				student = member.getReceipt().getStudent();
				Object[] receipts = student.getOfferingReceipts().toArray();
				for (int j = 0; j < receipts.length; j++) {
					AbstractReceipt receipt = (AbstractReceipt)receipts[j];
					if (DateUtil.isBetween(receipt.getDate(), startDate, endDate)) {
						if (receipt.getFee().getAmount() > 0) free++;
						else noFree++;
					}
				}
			}
			double prevPercent = findContinueStudyPreviousPercent(startDate2, endDate2, programIds[i]);
						
			//Object[] preiousValues = (Object[]) StudentServiceImpl.INSTANCE.findByDateNProgram(month2, year2, ProgramServiceImpl.INSTANCE.findById(programIds[i]));
			//double nPrevPercents = (((BigInteger)preiousValues[1]).intValue() != 0? ((double)((BigInteger)preiousValues[3]).intValue() + (double)((BigInteger)preiousValues[4]).intValue()) / (double)((BigInteger)preiousValues[1]).intValue() : 0);
			rowCount++;
			row = sheet.createRow(rowCount);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_N12);
			cell.setCellValue(i+1);
			
			cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(style_N12);
			cell.setCellValue(new HSSFRichTextString(programNames[i]));
			
			cell = row.createCell(2, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_N12);
			cell.setCellValue(size);
			
			cell = row.createCell(3, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_N12);
			cell.setCellValue(free);
			
			cell = row.createCell(4, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_N12);
			cell.setCellValue(noFree);
			
			cell = row.createCell(5, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_N12_percent);
			if (size > 0)
				cell.setCellFormula("(D"+(rowCount+1)+"+E"+(rowCount+1)+")/C"+(rowCount+1));
			
			cell = row.createCell(6, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_N12);
			sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount, 5, 6));
			
			cell = row.createCell(7, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_N12_percent);
			cell.setCellValue(prevPercent);
		}
		
		row = sheet.createRow(++rowCount);
		cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
		cell.setCellStyle(styleCenter_B12);
		cell.setCellValue(new HSSFRichTextString("Tổng cộng"));
		sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount, 0, 1)); // first row, last row, first column, last column
		
		cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
		cell.setCellStyle(styleCenter_B12);
		
		cell = row.createCell(2, HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellStyle(styleCenter_B12);
		cell.setCellFormula("SUM(C"+firstRow+":C"+rowCount+")");
		
		cell = row.createCell(3, HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellStyle(styleCenter_B12);
		cell.setCellFormula("SUM(D"+firstRow+":D"+rowCount+")");
		
		cell = row.createCell(4, HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellStyle(styleCenter_B12);
		cell.setCellFormula("SUM(E"+firstRow+":E"+rowCount+")");
		
		cell = row.createCell(5, HSSFCell.CELL_TYPE_STRING);
		cell.setCellStyle(styleCenter_B12);
		cell.setCellValue(new HSSFRichTextString(""));
		
		cell = row.createCell(6, HSSFCell.CELL_TYPE_STRING);
		cell.setCellStyle(styleCenter_B12);
		cell.setCellValue(new HSSFRichTextString(""));
		sheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount, 5, 6));
		
		cell = row.createCell(7, HSSFCell.CELL_TYPE_STRING);
		cell.setCellStyle(styleCenter_B12);
		cell.setCellValue(new HSSFRichTextString(""));
		
		return workbook;
	}

	private double findContinueStudyPreviousPercent(Date startDate, Date endDate, Long programId) {
		Collection<OfferingMember> prevMembers = OfferingMemberService.INSTANCE
			.findByDateAndProgram(startDate, endDate, ProgramServiceImpl.INSTANCE.findById(programId));
		Student prevStudent = null;
		
		double prevFree = 0;
		double prevNoFree = 0;

		for (OfferingMember member : prevMembers) {
			prevStudent = member.getReceipt().getStudent();
			Object[] receipts = prevStudent.getOfferingReceipts().toArray();
			for (int j = 0; j < receipts.length; j++) {
				AbstractReceipt receipt = (AbstractReceipt)receipts[j];
				if (DateUtil.isBetween(receipt.getDate(), startDate, endDate)) {
					if (receipt.getFee().getAmount() > 0) prevFree++;
					else prevNoFree++;
				}
			}
		}
		return (prevMembers.size() > 0 ? ((prevFree + prevNoFree) / prevMembers.size()) : 0);
	}

	public int getMonth() {
		return month;
	}

	public void setMonth(int month) {
		this.month = month;
	}

	public int getYear() {
		return year;
	}

	public void setYear(int year) {
		this.year = year;
	}
}